package com.css.zfzx.sjcj.modules.hddcwyDrillHole.repository.impl;

import com.css.zfzx.sjcj.modules.hddcwyDrillHole.repository.HddcWyDrillholeNativeRepository;
import com.css.zfzx.sjcj.modules.hddcwyDrillHole.repository.entity.HddcWyDrillholeEntity;
import com.css.zfzx.sjcj.modules.hddcwyDrillHole.viewobjects.HddcWyDrillholeQueryParams;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.TemporalType;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
/**
 * @author zyb
 * @date 2020-12-01
 */
@Repository
public class HddcWyDrillholeNativeRepositoryImpl implements HddcWyDrillholeNativeRepository {
    @PersistenceContext
    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(HddcWyDrillholeNativeRepositoryImpl.class);


    @Override
    public Page<HddcWyDrillholeEntity> queryHddcWyDrillholes(HddcWyDrillholeQueryParams queryParams , int curPage,
                                                             int pageSize) {
        StringBuilder sql = new StringBuilder("select * from hddc_wy_drillhole ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province = :province");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city = :city");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area = :area");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            whereSql.append(" and project_name like :projectName");
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), HddcWyDrillholeEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from hddc_wy_drillhole ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            query.setParameter("province", queryParams.getProvince());
            countQuery.setParameter("province", queryParams.getProvince());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            query.setParameter("city", queryParams.getCity());
            countQuery.setParameter("city", queryParams.getCity());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            query.setParameter("area", queryParams.getArea());
            countQuery.setParameter("area", queryParams.getArea());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            query.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
            countQuery.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
        }
        Pageable pageable = PageRequest.of(curPage - 1, pageSize);
        query.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
        query.setMaxResults(pageable.getPageSize());
        List<HddcWyDrillholeEntity> list = query.getResultList();
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        Page<HddcWyDrillholeEntity> hddcWyDrillholePage = new PageImpl<>(list, pageable, count.longValue());
        return hddcWyDrillholePage;
    }

    @Override
    public BigInteger queryHddcWyDrillhole(HddcWyDrillholeQueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from hddc_wy_drillhole ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province = :province");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city = :city");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area = :area");
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            whereSql.append(" and project_name like :projectName");
        }
        String startDate=queryParams.getStartTime();
        String endDate=queryParams.getEndTime();
        // 时间段
        Date start = null;
        Date end = null;
        if (!StringUtils.isEmpty(startDate) || !StringUtils.isEmpty(endDate)){
            try {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                // 结束时间为空
                if (StringUtils.isEmpty(endDate)) {
                    startDate += " 00:00:00";
                    start = sdf.parse(startDate);
                    whereSql.append(" and create_time >= :start");
                }
                // 开始时间为空
                else if (StringUtils.isEmpty(startDate)) {
                    endDate += " 23:59:59";
                    end = sdf.parse(endDate);
                    whereSql.append(" and create_time <= :end");
                }
                // 都不为空
                else {
                    startDate += " 00:00:00";
                    start = sdf.parse(startDate);
                    endDate += " 23:59:59";
                    end = sdf.parse(endDate);
                    whereSql.append(" and create_time between :start and :end");
                }
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), HddcWyDrillholeEntity.class);
        StringBuilder countSql = new StringBuilder("select count(*) from hddc_wy_drillhole ");
        Query countQuery = this.em.createNativeQuery(countSql.append(whereSql).toString());
        if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            query.setParameter("province", queryParams.getProvince());
            countQuery.setParameter("province", queryParams.getProvince());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            query.setParameter("city", queryParams.getCity());
            countQuery.setParameter("city", queryParams.getCity());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            query.setParameter("area", queryParams.getArea());
            countQuery.setParameter("area", queryParams.getArea());
        }
        if(!PlatformObjectUtils.isEmpty(queryParams.getProjectName())) {
            query.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
            countQuery.setParameter("projectName", "%" + queryParams.getProjectName() + "%");
        }

        // 时间
        if (!StringUtils.isEmpty(startDate) || !StringUtils.isEmpty(endDate)){
            // 结束时间为空
            if (StringUtils.isEmpty(endDate)) {
                countQuery.setParameter("start", start, TemporalType.DATE);
            }
            // 开始时间为空
            else if (StringUtils.isEmpty(startDate)) {
                countQuery.setParameter("end", end, TemporalType.DATE);
            }
            // 都不为空
            else {
                countQuery.setParameter("start", start, TemporalType.DATE);
                countQuery.setParameter("end", end, TemporalType.DATE);
            }
        }
        BigInteger count = (BigInteger) countQuery.getSingleResult();
        return count;
    }
}
